package com.isyscore.os.metadata.manager;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.isyscore.os.core.exception.DataFactoryException;
import com.isyscore.os.core.exception.ErrorCode;
import com.isyscore.os.core.sqlcore.Builder;
import com.isyscore.os.metadata.constant.CommonConstant;
import com.isyscore.os.metadata.database.*;
import com.isyscore.os.metadata.enums.DataSourceTypeEnum;
import com.isyscore.os.metadata.model.dto.ConditionDTO;
import com.isyscore.os.metadata.model.dto.DataSourceDTO;
import com.isyscore.os.metadata.model.dto.OrderByDTO;
import com.isyscore.os.metadata.model.vo.*;
import com.isyscore.os.metadata.properties.SqlQueryProperties;
import com.isyscore.os.metadata.service.TableAliasService;
import com.isyscore.os.metadata.service.impl.MetricDimFilterService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.annotation.PostConstruct;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Description :
 * @Author: qkc
 * @Date: 2021/9/28 13:40
 */
@Component
@Slf4j
public class DatabaseManager {

    @Autowired
    private MetricDimFilterService metricDimFilterService;
    @Autowired
    private SqlQueryProperties sqlQueryProperties;
    @Autowired
    private TableAliasService tableAliasService;
    private static SqlQueryProperties sqlQueryPropertiesStatic;
    private static final Map<DataSourceTypeEnum, AbstractDatabase> DB_MAP = new LinkedHashMap<>();

    public DatabaseManager() {
        DB_MAP.put(DataSourceTypeEnum.MYSQL, new MysqlDatabase());
        DB_MAP.put(DataSourceTypeEnum.MARIADB, new MariaDBDatabase());
        DB_MAP.put(DataSourceTypeEnum.ORACLE, new OracleDatabase());
        DB_MAP.put(DataSourceTypeEnum.SQLSERVER, new SqlServerDatabase());
        DB_MAP.put(DataSourceTypeEnum.CLICKHOUSE, new ClickHouseDatabase());
        DB_MAP.put(DataSourceTypeEnum.DM, new DmDatabase());
        DB_MAP.put(DataSourceTypeEnum.TD, new TDDatabase());
        DB_MAP.put(DataSourceTypeEnum.PGSQL, new PGSQLDatabase());
        DB_MAP.put(DataSourceTypeEnum.KINGBASE, new KingbaseDatabase());
    }

    public static AbstractDatabase findDb(DataSourceDTO dataSourceDTO) {
        DataSourceTypeEnum type = DataSourceTypeEnum.getType(dataSourceDTO.getType());
        AbstractDatabase db = DB_MAP.get(type);
        if (db == null) {
            throw new DataFactoryException(ErrorCode.DATA_SOURCE_CONNECTION_ERROR);
        }
        db.setQueryTimeOut(sqlQueryPropertiesStatic.getQueryTimeOut());
        return db;
    }

    @PostConstruct
    private void initTimeOut(){
        sqlQueryPropertiesStatic=this.sqlQueryProperties;
    }

    /**
     * 获取指定数据库下表列表
     *
     * @param dataSourceDTO
     * @return
     */
    public ResultVO getTableList(DataSourceDTO dataSourceDTO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String dbName = db.escapeDbName(dataSourceDTO.getSelectDatabaseName());
        String sql = db.tableListSql(dbName);
        if (StringUtils.isEmpty(sql)) {
            throw new DataFactoryException(ErrorCode.SELECT_NOT_SUPPORT);
        }
        ResultVO resultVO = db.execSqlAndGet(dataSourceDTO, sql);
        db.postTableList(resultVO);
        return resultVO;
    }

    public ResultVO getTableListWithAlias(DataSourceDTO dataSourceDTO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String dbName = db.escapeDbName(dataSourceDTO.getSelectDatabaseName());
        String sql = db.tableListSql(dbName);
        if (StringUtils.isEmpty(sql)) {
            throw new DataFactoryException(ErrorCode.SELECT_NOT_SUPPORT);
        }
        ResultVO resultVO = db.execSqlAndGet(dataSourceDTO, sql);
        for (Map<String, Object> map : resultVO.getContent()) {
            Map<String, String> allAliasCache = tableAliasService.getAllAliasCache();
            String tableName = (String) map.get("tableName");
            String key = dataSourceDTO.getId()+dbName+tableName;
            if(allAliasCache.containsKey(key)){
                map.put("tableName",tableName);
                map.put("tableAlias",allAliasCache.get(key));
            }else{
                map.put("tableAlias","");
            }
            db.postTableList(resultVO);
        }

        return resultVO;
    }

    /**
     * 获取表结构
     *
     * @param dataSourceDTO
     * @return
     */
    public ResultVO getTableStruct(DataSourceDTO dataSourceDTO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String dbName = db.escapeDbName(dataSourceDTO.getSelectDatabaseName());
        String sql = db.tableStructSql(dataSourceDTO.getTableName(), dbName);

        ResultVO resultVO = db.execSqlAndGet(dataSourceDTO, sql);
        db.postTableStruct(resultVO);
        String tableComment = Optional.ofNullable(resultVO.getContent())
                .filter(item -> item.size() > 0)
                .map(item -> item.get(CommonConstant.DEFAULT_INDEX))
                .map(item -> item.get("tableComment"))
                .map(o -> (String) o)
                .orElse(null);
        if (null != tableComment) {
            resultVO.getContent().forEach(stringObjectMap -> stringObjectMap.remove("tableComment"));
        }
        resultVO.setTableComment(tableComment);
        return resultVO;
    }

    //
    public ResultVO getLinkTable(DataSourceDTO dataSourceDTO) {
        List<String> columnNameList = dataSourceDTO.getColumnNameList();
        StringBuilder columns = new StringBuilder();
        for (int i = 0; i < columnNameList.size(); i++) {
            columns.append("'").append(columnNameList.get(i)).append("'");
            if (i < columnNameList.size() - 1) {
                columns.append(",");
            }
        }
        AbstractDatabase db = findDb(dataSourceDTO);
        String dbName = db.escapeDbName(dataSourceDTO.getSelectDatabaseName());
        String sql = db.linkTableSql(columns.toString(), dbName);

        return db.execSqlAndGet(dataSourceDTO, sql);
    }

    public void renameTable(DataSourceDTO dataSourceDTO, TableVO tableVO) {
        dataSourceDTO.setTableName(tableVO.getNewTableName());
        String oldTableName = tableVO.getOldTableName();
        String newTableName = tableVO.getNewTableName();
        if (!oldTableName.equals(newTableName)) {
            AbstractDatabase db = findDb(dataSourceDTO);
            oldTableName = db.escapeTbName(oldTableName, dataSourceDTO.getSelectDatabaseName());
            String sql = db.renameTableSql(oldTableName, newTableName);

            log.debug("add batch rename table sql: {}", sql);
            try {
                db.execSql(dataSourceDTO, sql, null);
            } catch (DataFactoryException e) {
                throw new DataFactoryException(ErrorCode.RENAME_TABLE_FAILED);
            }
        }
    }

    //此处有两个调用
    //sql编辑器手动写SQL也会调用此处来查询数据
    public ResultVO page(DataSourceDTO dataSourceDTO, DataQueryVO dataQueryVO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String pageSql;
        int offset = (dataQueryVO.getCurrent() - 1) * dataQueryVO.getSize();
        int limit = dataQueryVO.getSize();
        if (StringUtils.isBlank(dataQueryVO.getSqlText())) {
            String sqlText = "select * from " + db.escapeTbName(dataSourceDTO.getTableName(), dataSourceDTO.getSelectDatabaseName());
            sqlText += extractCondition(dataQueryVO.getCondition(), dataQueryVO.getOrderBy(), db);
            pageSql = db.pageSql(sqlText, offset, limit);
        } else {
            pageSql = db.pageSql(dataQueryVO.getSqlText(), offset, limit);
        }
        ResultVO resultVO;
        if (CollectionUtil.isNotEmpty(dataQueryVO.getParams())) {
            resultVO = db.execSqlAndGetByParams(dataSourceDTO, pageSql, dataQueryVO.getParams());
        } else {
            resultVO = db.execSqlAndGet(dataSourceDTO, pageSql);
        }
        if (dataQueryVO.isSearchCount()) {
            Long count = count(dataSourceDTO, dataQueryVO);
            resultVO.setCount(count);
        }
        return resultVO;
    }

    private String extractCondition(List<ConditionDTO> condition, List<OrderByDTO> orderBy, AbstractDatabase db) {
        //提取查询条件
        String res = "";
        if (condition != null && !condition.isEmpty()) {
            res += " WHERE ";
            res += condition.stream().map(c -> c.transferTO()).map(filter -> metricDimFilterService.parseFilter2Sql(db, filter))
                    .collect(Collectors.joining(" AND "));
        }
        if (orderBy != null && !orderBy.isEmpty()) {
            res = res + " ORDER BY " + orderBy.stream().map(order -> db.escapeColName(order.getColName()) + " "
                    + (StringUtils.isBlank(order.getRule()) ? "ASC" : order.getRule())).collect(Collectors.joining(","));
        }

        return res;
    }


    public Long count(DataSourceDTO dataSourceDTO, DataQueryVO dataQueryVO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String sqlText = dataQueryVO.getSqlText();
        String countSql;
        if (StringUtils.isBlank(sqlText)) {
            countSql = "select count(1) from " + db.escapeTbName(dataSourceDTO.getTableName(), dataSourceDTO.getSelectDatabaseName());
        } else {
            countSql = db.countSql(sqlText);
        }
        ResultVO countData;
        if (CollectionUtil.isNotEmpty(dataQueryVO.getParams())) {
            countData = db.execSqlAndGetByParams(dataSourceDTO, countSql, dataQueryVO.getParams());
        } else {
            countData = db.execSqlAndGet(dataSourceDTO, countSql);
        }
        if (countData.getContent().size() > 0) {
            Object total = countData.getContent().get(0).values().toArray()[0];
            if (total != null) {
                return Long.parseLong(String.valueOf(total));
            }
        }
        return 0L;
    }


    public String transFieldFormat(DataSourceDTO dataSourceDTO, String field, String filedType){
        AbstractDatabase db = findDb(dataSourceDTO);
        //是否为时间类型
        if(!db.isLegalDateType(filedType)){
            DataQueryVO dataQueryVO = new DataQueryVO();
            dataQueryVO.setCurrent(1);
            dataQueryVO.setSize(1);
            ResultVO tableData = getTableData(dataSourceDTO, dataQueryVO);
            if(tableData.getCount()==0){
                return null;
            }else{
                DateTime dateTime =null;
                try {
                    dateTime = DateUtil.parseDateTime(String.valueOf(tableData.getContent().get(0).get(field)));
                }catch (Exception e){
                }
                if(dateTime!=null){
//                   return db.getParseStr2DateEl(db.escapeColName(field));
                    return db.escapeColName(field);
                }else{
                    //不是时间的字符串
                    return db.getParseStr2IntEl(db.escapeColName(field));
                }
            }
        }else{ //是时间类型
           return db.escapeColName(field);
        }
    }

    public Object maxValue(DataSourceDTO dataSourceDTO, String field) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String sql = "select max("+field +") from " + db.escapeTbName(dataSourceDTO.getTableName(), dataSourceDTO.getSelectDatabaseName());
        log.debug("max sql:"+sql);
        ResultVO countData = db.execSqlAndGet(dataSourceDTO, sql);
        return  countData.getContent().get(0).values().toArray()[0];
    }

    public Object minValue(DataSourceDTO dataSourceDTO, String field) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String sql = "select min("+field+") from " + db.escapeTbName(dataSourceDTO.getTableName(), dataSourceDTO.getSelectDatabaseName());
        log.debug("min sql:"+sql);
        ResultVO countData = db.execSqlAndGet(dataSourceDTO, sql);
        return  countData.getContent().get(0).values().toArray()[0];
    }

    public ResultVO getTableData(DataSourceDTO dataSourceDTO, DataQueryVO dataQueryVO) {
        AbstractDatabase db = findDb(dataSourceDTO);
        dataQueryVO.setSearchCount(true);
        ResultVO resultVO = page(dataSourceDTO, dataQueryVO);
        db.wrapTableData(resultVO);
        return resultVO;
    }

    @Transactional(rollbackFor = Exception.class)
    public void create(DataSourceDTO dataSourceDTO, List<Pair<ColumnInfoVo, Integer>> mappings, ExcelDataVo param) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String tableName = db.escapeTbName(param.getDataTableName(), param.getDatabaseName());

        Assert.notNull(db.builder, "建表不可为空");
        //建表
        Builder tableBuilder = db.builder.createTable(tableName);
        mappings.forEach(pair -> {
            ColumnInfoVo left = pair.getLeft();
            String colName = left.getAliasName() == null ? left.getEnName() : left.getAliasName();
            tableBuilder.addCol(colName, db.COLUMN_TYPE.get(left.getDataType()), left.getComment());
        });
        String createTableSql = tableBuilder.build();
        db.execSql(dataSourceDTO, createTableSql, null);
        log.info("新建表格{}成功", tableName);
    }

    @Transactional(rollbackFor = Exception.class)
    public List<String> insert(DataSourceDTO dataSourceDTO, List<Pair<ColumnInfoVo, Integer>> mappings, List<Object[]> cols, ExcelDataVo param) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String tableName = db.escapeTbName(param.getDataTableName(), param.getDatabaseName());

        String resTemplate = "第%s行插入：%s";
        List<String> res = new ArrayList<>();
        int end = 0;
        try (Connection conn = db.dataSourceManager.getConn(dataSourceDTO);//获取数据库连接
             Statement statement = conn.createStatement()) {
            db.afterCreateStatement(statement, dataSourceDTO);
            for (int i = 0; i < cols.size(); i++) {
                end++;
                Object[] colVal = cols.get(i);
                Builder sqlBuilder = db.builder.insert(tableName);
                mappings.forEach(pair -> {
                    String colName = pair.getLeft().getAliasName() == null ? pair.getLeft().getEnName() : pair.getLeft().getAliasName();
                    sqlBuilder.addValue(colName, colVal[pair.getRight()]);
                });
                String sql = sqlBuilder.build();
                try {
                    log.debug(sql);
                    statement.addBatch(sql);
                    if ((end + 10) % 10 == 0 || end == cols.size()) {
                        int[] resLoop = statement.executeBatch();
                        statement.clearBatch();
                        for (int value : resLoop) {
                            if (value < 0) {
                                log.info("第{}行插入：{}", end, "SUCCESS");
                            }
                        }
                    }
                } catch (SQLException e) {
                    log.error(e.getMessage());
                    log.info("第{}行插入：{}", end, "FAILED");
                    res.add(String.format(resTemplate, end, "FAILED"));
                }
            }
        } catch (Throwable e) {
//            log.error("execute sql:{}, error:{}",sql, e.getMessage());
            String message = DataFactoryException.getMessage(e);
            throw new DataFactoryException(ErrorCode.COMMON_ERROR, message);
        }
        if (res.isEmpty()) {
            res.add("数据导入成功");
        }
        return res;
    }

    @Transactional(rollbackFor = Exception.class)
    public void delete(DataSourceDTO dataSourceDTO, ExcelDataVo param) {
        AbstractDatabase db = findDb(dataSourceDTO);
        String tableName = db.escapeTbName(param.getDataTableName(), param.getDatabaseName());

        String sql = db.dropTableSql(tableName);
        db.execSql(dataSourceDTO, sql, null);
        log.info("删除表格{}成功", tableName);
    }

}
